package service

import (
	"database/sql"
	"log"
	"strconv"
	"time"
)

///添加员工
func (db DB) AddEmployee(reqMap map[string]interface{}) bool {
	//从json中提取参数
	deptId := int(reqMap["deptId"].(float64))
	name := reqMap["name"].(string)
	position := reqMap["position"].(string)
	var phone interface{}
	if reqMap["phone"] != nil {
		phone = reqMap["phone"].(string)
	}
	var office interface{}
	if reqMap["office"] != nil {
		office = reqMap["office"].(string)
	}
	var gender interface{}
	if reqMap["gender"] != nil {
		gender = int(reqMap["gender"].(float64))
	}
	var birthday interface{}
	if reqMap["birthday"] != nil {
		timeVal, err := time.Parse("2006-01-02", reqMap["birthday"].(string))
		if err != nil {
			log.Println("time.Parse error: ", err)
		} else {
			birthday = timeVal
		}
	}
	var bak interface{}
	if reqMap["bak"] != nil {
		bak = reqMap["bak"].(string)
	}
	// log.Println("=============参数OK")

	//SQL预处理
	sql := "INSERT INTO employee(dept_id,name,position,phone,office,gender,birthday,bak)VALUES(?,?,?,?,?,?,?,?)"
	stmt, err := db.Conn.Prepare(sql)
	if err != nil {
		log.Println("SQL Prepare error: ", err)
		return false
	}
	//SQL执行
	_, err = stmt.Exec(deptId, name, position, phone, office, gender, birthday, bak)
	if err != nil {
		log.Println("SQL Exec error: ", err)
		return false
	}

	return true
}

///删除员工
func (db DB) DelEmployee(reqMap map[string]interface{}) bool {
	//从json中提取参数
	id := int(reqMap["id"].(float64))

	//SQL预处理
	sql := "DELETE FROM employee WHERE id=?"
	stmt, err := db.Conn.Prepare(sql)
	if err != nil {
		log.Println("SQL Prepare error: ", err)
		return false
	}
	//SQL执行
	_, err = stmt.Exec(id)
	if err != nil {
		log.Println("SQL Exec error: ", err)
		return false
	}

	return true
}

///修改员工
func (db DB) EditEmployee(reqMap map[string]interface{}) bool {
	//从json中提取参数
	id := int(reqMap["id"].(float64))
	deptId := int(reqMap["deptId"].(float64))
	name := reqMap["name"].(string)
	position := reqMap["position"].(string)
	var phone interface{}
	if reqMap["phone"] != nil {
		phone = reqMap["phone"].(string)
	}
	var office interface{}
	if reqMap["office"] != nil {
		office = reqMap["office"].(string)
	}
	var gender interface{}
	if reqMap["gender"] != nil {
		gender = int(reqMap["gender"].(float64))
	}
	var birthday interface{}
	if reqMap["birthday"] != nil {
		timeVal, err := time.Parse("2006-01-02", reqMap["birthday"].(string))
		if err != nil {
			log.Println("time.Parse error: ", err)
		} else {
			birthday = timeVal
		}
	}
	var bak interface{}
	if reqMap["bak"] != nil {
		bak = reqMap["bak"].(string)
	}
	//SQL预处理
	sql := "UPDATE employee SET dept_id=?,name=?,position=?,phone=?,office=?,gender=?,birthday=?,bak=? WHERE id=?"
	stmt, err := db.Conn.Prepare(sql)
	if err != nil {
		log.Println("SQL Prepare error: ", err)
		return false
	}
	//SQL执行
	_, err = stmt.Exec(deptId, name, position, phone, office, gender, birthday, bak, id)
	if err != nil {
		log.Println("SQL Exec error: ", err)
		return false
	}

	return true
}

//获得员工信息
func (db DB) GetEmployee(reqMap map[string]interface{}) map[string]interface{} {
	//从json中提取参数
	id := int(reqMap["id"].(float64))

	//查询SQL
	sqlstr := "SELECT e.id,e.dept_id,d.name as dept_name,e.name,e.position,e.phone,e.office,e.gender,e.birthday,e.bak " +
		"FROM employee e INNER JOIN dept d ON e.dept_id=d.id WHERE e.id=?"
	rows, err := db.Conn.Query(sqlstr, id)
	if err != nil {
		log.Println("Query error: ", err)
		rspEmpty := make(map[string]interface{})
		return rspEmpty
	}

	defer rows.Close() //方法返回时rows将关闭

	//查询结果集
	var (
		deptId   int
		deptName string
		name     string
		position string
		phone    string
		office   sql.NullString
		gender   sql.NullInt16
		birthday sql.NullTime
		bak      sql.NullString
	)
	if !rows.Next() {
		log.Println("Can't get the employee, id=", id)
		rspEmpty := make(map[string]interface{})
		return rspEmpty
	}
	if err = rows.Scan(&id, &deptId, &deptName, &name, &position, &phone, &office, &gender, &birthday, &bak); err != nil {
		log.Println("rows scan err: ", err)
		rspEmpty := make(map[string]interface{})
		return rspEmpty
	}

	//返回
	rsp := make(map[string]interface{})
	rsp["id"] = id
	rsp["deptId"] = deptId
	rsp["deptName"] = deptName
	rsp["name"] = name
	rsp["position"] = position
	rsp["phone"] = phone
	if office.Valid {
		rsp["office"] = office.String
	} else {
		rsp["office"] = nil
	}
	if gender.Valid {
		rsp["gender"] = gender.Int16
	} else {
		rsp["gender"] = nil
	}
	if birthday.Valid {
		rsp["birthday"] = birthday.Time.Format("2006-01-02")
	} else {
		rsp["birthday"] = nil
	}
	if bak.Valid {
		rsp["bak"] = bak.String
	} else {
		rsp["bak"] = nil
	}
	return rsp
}

//用户列表（分页）
func (db DB) PageListEmployee(reqMap map[string]interface{}) map[string]interface{} {
	//从json中提取参数
	queryField := reqMap["queryField"].(map[string]interface{}) //查询条件
	pageNo := int(reqMap["pageNo"].(float64))                   //页码（从0开始）
	pageCount := int(reqMap["pageCount"].(float64))             //每页条数

	//获取查询结果总条数
	total := db.totalEmployee(queryField)
	//总页数
	var totalPage int
	if total%pageCount == 0 {
		totalPage = total / pageCount
	} else {
		totalPage = total/pageCount + 1
	}
	//是否有上一页
	hadPre := pageNo > 0
	//是否有下一页
	hadNext := pageNo+1 < totalPage
	//本页列表数据
	list := db.queryEmployee(queryField, pageNo, pageCount)

	rsp := make(map[string]interface{})
	rsp["pageNo"] = pageNo       //页码（从0开始）
	rsp["pageCount"] = pageCount //每页条数
	rsp["total"] = total         //查询结果总条数
	rsp["totalPage"] = totalPage //查询结果总页数
	rsp["hadPre"] = hadPre       //是否有上一页
	rsp["hadNext"] = hadNext     //是否有下一页
	rsp["list"] = list           //本页数据
	return rsp
}

// 总条数
func (db DB) totalEmployee(queryField map[string]interface{}) int {

	//根据查询字段拼接sql
	sql := "SELECT count(*) FROM employee WHERE 1=1"
	//部门id
	if queryField["deptId"] != nil {
		sql = sql + " AND dept_id=" + strconv.Itoa(queryField["deptId"].(int))
	}
	//姓名
	if queryField["name"] != nil {
		sql = sql + " AND name like '%" + queryField["name"].(string) + "%'"
	}
	//职务
	if queryField["position"] != nil {
		sql = sql + " AND position like '%" + queryField["position"].(string) + "%'"
	}
	//手机
	if queryField["phone"] != nil {
		sql = sql + " AND phone like '%" + queryField["phone"].(string) + "%'"
	}
	//办公室
	if queryField["office"] != nil {
		sql = sql + " AND office like '%" + queryField["office"].(string) + "%'"
	}
	//性别（1男 2女）
	if queryField["gender"] != nil {
		sql = sql + " AND gender=" + strconv.Itoa(queryField["gender"].(int))
	}
	//出生年月日
	if queryField["gender"] != nil {
		sql = sql + " AND birthday='" + queryField["gender"].(string) + "'"
	}
	//备注
	if queryField["bak"] != nil {
		sql = sql + " AND bak like '%" + queryField["bak"].(string) + "%'"
	}

	//查询
	rows, err := db.Conn.Query(sql)
	if err != nil {
		log.Println("totalEmployee Query error: ", err)
		return 0
	}

	defer rows.Close() //方法返回时rows将关闭

	rows.Next()
	var total int
	rows.Scan(&total)

	return total
}

// 用户列表（分页查询）
func (db DB) queryEmployee(queryField map[string]interface{}, pageNo int, pageCount int) []map[string]interface{} {
	//根据查询字段拼接sql
	sqlstr := "SELECT e.id,e.dept_id,d.name as dept_name,e.name,e.position,e.phone,e.office,e.gender,e.birthday,e.bak " +
		"FROM employee e INNER JOIN dept d ON e.dept_id=d.id WHERE 1=1"
	//部门id
	if queryField["deptId"] != nil {
		sqlstr = sqlstr + " AND e.dept_id=" + strconv.Itoa(queryField["deptId"].(int))
	}
	//姓名
	if queryField["name"] != nil {
		sqlstr = sqlstr + " AND e.name like '%" + queryField["name"].(string) + "%'"
	}
	//职务
	if queryField["position"] != nil {
		sqlstr = sqlstr + " AND e.position like '%" + queryField["position"].(string) + "%'"
	}
	//手机
	if queryField["phone"] != nil {
		sqlstr = sqlstr + " AND e.phone like '%" + queryField["phone"].(string) + "%'"
	}
	//办公室
	if queryField["office"] != nil {
		sqlstr = sqlstr + " AND e.office like '%" + queryField["office"].(string) + "%'"
	}
	//性别（1男 2女）
	if queryField["gender"] != nil {
		sqlstr = sqlstr + " AND e.gender=" + strconv.Itoa(queryField["gender"].(int))
	}
	//出生年月日
	if queryField["gender"] != nil {
		sqlstr = sqlstr + " AND e.birthday='" + queryField["gender"].(string) + "'"
	}
	//备注
	if queryField["bak"] != nil {
		sqlstr = sqlstr + " AND e.bak like '%" + queryField["bak"].(string) + "%'"
	}
	//分页
	sqlstr = sqlstr + " LIMIT " + strconv.Itoa(pageNo*pageCount) + "," + strconv.Itoa(pageCount)

	rows, err := db.Conn.Query(sqlstr)
	if err != nil {
		log.Println("Query error: ", err)
		rspEmpty := make([]map[string]interface{}, 0)
		return rspEmpty
	}

	defer rows.Close() //方法返回时rows将关闭

	var list []map[string]interface{} //返回集合
	//查询结果集
	var (
		id       int
		deptId   int
		deptName string
		name     string
		position string
		phone    sql.NullString
		office   sql.NullString
		gender   sql.NullInt16
		birthday sql.NullTime
		bak      sql.NullString
	)
	for rows.Next() {
		if err = rows.Scan(&id, &deptId, &deptName, &name, &position, &phone, &office, &gender, &birthday, &bak); err != nil {
			log.Println("rows scan err: ", err)
			break
		}

		m := make(map[string]interface{})
		m["id"] = id
		m["deptId"] = deptId
		m["deptName"] = deptName
		m["name"] = name
		m["position"] = position
		if phone.Valid {
			m["phone"] = phone.String
		} else {
			m["phone"] = nil
		}
		if office.Valid {
			m["office"] = office.String
		} else {
			m["office"] = nil
		}
		if gender.Valid {
			m["gender"] = gender.Int16
		} else {
			m["gender"] = nil
		}
		if birthday.Valid {
			m["birthday"] = birthday.Time.Format("2006-01-02")
		} else {
			m["birthday"] = nil
		}
		if bak.Valid {
			m["bak"] = bak.String
		} else {
			m["bak"] = nil
		}

		list = append(list, m)
	}

	return list
}
